package data;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import com.mysql.jdbc.Driver;

/**
	Handles all reading from, and updates to, MySQL database
	@author jkhsieh
*/
public class MySqlConnection
{
   private String connectionURL = "jdbc:mysql://localhost:3306/mojito?user=root;password=mojito";
   private Connection connection = null;
   private Statement statement = null;
   private ResultSet rs = null;
   
   
   public MySqlConnection() throws InstantiationException, 
      IllegalAccessException, ClassNotFoundException, SQLException
   {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      connection = DriverManager.getConnection(connectionURL, "root", "mojito");
      statement = connection.createStatement();
   }
   
   
   /* ----------------------------------------------------------------------------- */
   /**
   Makes and returns a User object only if username, password params match a record
   in the database.
   
   users table fields: username, password, name, groupID, email
   User object fields: userName, name, groupID, admin, email, userCalendar
   
   @throws SQLException
    */
   public User getUser(String username, String password) throws SQLException
   {
      rs = statement.executeQuery("SELECT * FROM users WHERE username='" + username + 
            "' AND password='" + password + "'");
      
      if(!rs.next()) return null;
      
      User user = new User(rs.getString("username"),
            rs.getString("name"), rs.getInt("groupID"), rs.getString("email"));
      //admin status set by getGroup(user)
      rs.close();
      
      return user;
   }
   
   
   /**
      Updates user record from User object and/or new password String.
      @throws SQLException  */
   public void updateUser(User user, String newPassword) throws SQLException
   {
      String query = "UPDATE mojito.users SET name='"+
         user.getRealName() +"',password='"+ newPassword +"',email='"+ user.getEmail() +
         "' WHERE username='" + user.getUserName() +"'";
      System.out.println("query string: " + query);
      statement.executeUpdate(query);
   }
   
   /**
      Updates user record from User object
      @throws SQLException  */
   public void updateUser(User user) throws SQLException
   {
      String query = "UPDATE mojito.users SET name='"+
         user.getRealName() +"',email='"+ user.getEmail() +
         "' WHERE username='" + user.getUserName() +"'";
      System.out.println("query string: " + query);
      statement.executeUpdate(query);
   }
   
   
   /* ----------------------------------------------------------------------------- */
   /**
   Makes and returns a Group object for the User. Group members are defined.
   
   groups table fields: id, name, admin
   Group object fields: id, name, admin, users arraylist
   
   @throws SQLException
    */
   public Group getGroup(User user) throws SQLException
   {
      int groupID = user.getGroupID();
      
      //group
      rs = statement.executeQuery("SELECT * FROM groups WHERE id=" + groupID);
      if(!rs.next()) return null;
      String groupName = rs.getString("name");
      String adminName = rs.getString("admin");
      
      //members
      rs = statement.executeQuery("SELECT * FROM users WHERE groupID=" + groupID);
      if(!rs.next()) return null;
      
      Group group = new Group(groupID, groupName, adminName);
      do
      {
         User groupMember = new User( rs.getString("username"),
               rs.getString("name"), rs.getInt("groupID"), rs.getString("email"));
         
         if(groupMember.equals(user))
            groupMember = user;    //groupMember is duplicate of user; want handle same
         
         group.addUser(groupMember);
         if(groupMember.getUserName().equals(adminName))    //saved from prev rs
            groupMember.setAdmin(true);
         
      } while(rs.next());
      rs.close();
      
      return group;
   }//getGroup
   
   
   /* -----------------------------------------------------------------------------
   events table fields: id, name, groupID, user, start, end, description, ...
   Event object fields: id, name, groupID, user, type, startDate, endDate, description, ...
   */
   
   /**
      Removes an event from the database
      @throws SQLException 
    */
   public void removeEvent(int id) throws SQLException
   {
      String query = "DELETE FROM mojito.events WHERE id=" + id;
      System.out.println("query string: " + query);
      statement.executeUpdate(query);
   }
   
   /**
      Updates an event in the database
    */
   public void updateEvent(int id, Event e) throws SQLException
   {
      boolean[] recurForDays = e.getDaysOfWeek();
      String query = "UPDATE mojito.events SET name='" +  
         e.getEventName() +"',start='"+ 
         TwoPlaceString.getSqlTimestamp(e.getStartTime()) +"',end='"+ 
         TwoPlaceString.getSqlTimestamp(e.getEndTime()) +"',description='"+ 
         e.getDescription() +"',type='"+
         e.getType() +"',recurring="+ e.isRecurring() +",type='"+ e.getType() +"',sun="+
         recurForDays[1] +",mon="+ recurForDays[2] +",tue="+
         recurForDays[3] +",wed="+ recurForDays[4] +",thu="+
         recurForDays[5] +",fri="+ recurForDays[6] +",sat="+
         recurForDays[7] +" WHERE id="+ id;
      System.out.println("query string: " + query);
      statement.executeUpdate(query);
   }
   
   
   /**
   Adds event to database<br>
   post: Event ID set 
    */
   public void addEvent(Event e) throws SQLException
   {
      //build start, end date strings
      GregorianCalendar startCal = e.getStartTime();
      String startString = TwoPlaceString.getSqlTimestamp(startCal);
      GregorianCalendar endCal = e.getEndTime();
      String endString = TwoPlaceString.getSqlTimestamp(endCal);
      
      boolean[] recurForDays = e.getDaysOfWeek();
      
      String query = "INSERT INTO mojito.events VALUES(" + "default,'" + 
         e.getEventName() +"',"+ e.getGroupID() +",'"+ e.getUserName() +"','"+
         startString +"','"+ endString +"','"+ 
         e.getDescription() +"',"+ e.isRecurring() + ",'" + e.getType() + "'," +
         recurForDays[1] +","+ recurForDays[2] +","+ 
         recurForDays[3] +","+ recurForDays[4] +","+ recurForDays[5] +","+ 
         recurForDays[6] +","+ recurForDays[7] + 
      ")";
      System.out.println("query string: " + query);
      statement.executeUpdate(query);
       
      int groupOwner = e.getGroupID();
      String userOwner = e.getUserName();
      
      //Find in database and set object's id field...
      query = "SELECT * FROM mojito.events WHERE ";
      if(!userOwner.isEmpty())
      {
         query += "user='"+ userOwner + "'";
         //rs = statement.executeQuery(query);
      }
      else
      {
         query += "groupID=" + groupOwner;
         //rs = statement.executeQuery(query);
      }
      
      query += " AND start='" + TwoPlaceString.getSqlTimestamp(e.getStartTime()) +"'";
      System.out.println("query string: " + query);
      rs = statement.executeQuery(query);
      
      if(rs.next())
         e.setEventID(rs.getInt("id"));
      System.out.println(e);
   }//addEvent
   
   /**
      Gets an event from the database by id
    */
   public Event getEvent(int id) throws SQLException
   {
      //get records from database
      rs = statement.executeQuery("SELECT * FROM events WHERE id=" + id);
      if(!rs.next()) return null;
      
      return eventFromRecord(rs);
   }//getEvent
   
   
   /* helper for getEvent(id) and getEvents(query) 
      pre: rs.next() == true
    */
   private Event eventFromRecord(ResultSet rs) throws SQLException
   {
      Timestamp startTime = rs.getTimestamp("start");
      Timestamp endTime = rs.getTimestamp("end");
      
      GregorianCalendar start = new GregorianCalendar();
      start.setTime(startTime);
      GregorianCalendar end = new GregorianCalendar();
      end.setTime(endTime);
      
      Event e = new Event(
            rs.getInt("id"),
            rs.getString("name"),
            rs.getInt("groupID"),
            rs.getString("user"),
            rs.getString("type"),
            start,
            end,
            rs.getString("description"),
            
            rs.getBoolean("recurring"),
            rs.getBoolean("sun"),
            rs.getBoolean("mon"),
            rs.getBoolean("tue"),
            rs.getBoolean("wed"),
            rs.getBoolean("thu"),
            rs.getBoolean("fri"),
            rs.getBoolean("sat")
         );
      return e;
   }//eventFromRecord
   
   
   /* ----------------------------------------------------------------------------- */
   /**
   Get events for user
   @param startYMD is a String in the form yyyy-mm-dd
    */
   public ArrayList<Event> getEvents(String username, String startYMD, String endYMD) throws 
   SQLException
   {
      //get records from database, assume events start & end on same day or close enough
      String query = "SELECT * FROM mojito.events WHERE user='" +username+ 
         "' AND start BETWEEN '"+ startYMD + "' AND '"+ endYMD + "'";
      //System.out.println("query string: " + query);
      
      return getEvents(query);
   }
   
   
   /**
   Get events for group
   @param startYMD is a String in the form yyyy-mm-dd
    */
   public ArrayList<Event> getEvents(int groupID, String startYMD, String endYMD) throws SQLException
   {
      //get records from database, assume events start & end on same day or close enough
      String query = "SELECT * FROM mojito.events WHERE user='' AND groupID=" +groupID+ 
         " AND start BETWEEN '"+ startYMD + "' AND '"+ endYMD + "'";
      //System.out.println("query string: " + query);
      
      return getEvents(query);
   }
   
   
   /**
      Get ALL events for an individual user
      @param username username, or <blank> for group
    */
   public ArrayList<Event> getUserEvents(String username) throws 
   SQLException
   {
      //get records from database, assume events start & end on same day or close enough
      String query = "SELECT * FROM mojito.events WHERE user='" +username+"'";
      //System.out.println("query string: " + query);
      
      return getEvents(query);
   }
   
   /* helper method for getEvents methods for user and group */
   private ArrayList<Event> getEvents(String query) throws SQLException
   {
      rs = statement.executeQuery(query);
      if(!rs.next()) return null;
      
      ArrayList<Event> eventsList = new ArrayList<Event>();
      do
      {
         eventsList.add(eventFromRecord(rs));
         
      } while(rs.next());
      
      return eventsList;
   }//getEvents helper method
   
   
}//MySQLConnection


/**
A helper class to provide methods for writing SQL dates
 */
class TwoPlaceString
{
   /** 
   Get a SQL-friendly string representation of the calendar
    */
   public static String getSqlTimestamp(GregorianCalendar gc)
   {
      int year = gc.get(Calendar.YEAR);
      int month = gc.get(Calendar.MONTH) + 1;
      int day = gc.get(Calendar.DATE);
      int hour = gc.get(Calendar.HOUR_OF_DAY);
      int min = gc.get(Calendar.MINUTE);
      
      return TwoPlaceString.forInt(year) +"-"+ TwoPlaceString.forInt(month) +"-"+
            TwoPlaceString.forInt(day) +" "+ TwoPlaceString.forInt(hour) +":"+ 
            TwoPlaceString.forInt(min);      //yyyy-mm-dd hh:mm
   }
   
   /**
   Makes a length-two String---for month, day, hour, or minute 
    */
   public static String forInt(int input)
   {
      String output = "";
      if(input < 10) output += "0";    //put a 0 before single digit
      return output += input;
   }
}//TwoPlaceString